package com.cykj.util.excel;

import cn.hutool.extra.template.engine.TemplateFactory;
import com.cykj.pojo.CyPatient;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.ServletContext;
import java.io.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelOutputUtil {
    private static XSSFWorkbook xWorkbook = null;

    public static void createExcelXls(String fileDir, List<String> sheetNames, String titleRow[]){
        //创建workbook
        xWorkbook = new XSSFWorkbook();
        //新建文件
        FileOutputStream fileOutputStream = null;
        XSSFRow row = null;
        try {
            CellStyle cellStyle = xWorkbook.createCellStyle();
            cellStyle.setAlignment(HorizontalAlignment.LEFT);
            cellStyle.setVerticalAlignment(VerticalAlignment.BOTTOM);

            //添加Worksheet（不添加sheet时生成的xlsx文件打开时会报错)
            for(int i = 0; i<sheetNames.size(); i++){
                xWorkbook.createSheet(sheetNames.get(i));
                xWorkbook.getSheet(sheetNames.get(i)).createRow(0);
                //添加表头, 创建第一行
                row = xWorkbook.getSheet(sheetNames.get(i)).createRow(0);
                row.setHeight((short)(20*20));
                for (short j = 0; j < titleRow.length; j++) {
                    System.out.println("-----------"+titleRow[j]);
                    XSSFCell cell = row.createCell(j, CellType.BLANK);
                    cell.setCellValue(titleRow[j]);
                    cell.setCellStyle(cellStyle);
                }
                fileOutputStream = new FileOutputStream(fileDir);
                xWorkbook.write(fileOutputStream);
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {

            if (fileOutputStream != null) {
                try {
                    fileOutputStream.close();
                } catch (IOException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 删除文件.
     * @param fileDir  文件路径
     * @return 如果文件不存在返回false, 如果文件存在删除成功之后返回true
     */
    public static boolean deleteExcel(String fileDir) {
        boolean flag = false;
        File file = new File(fileDir);
        // 判断目录或文件是否存在
        if (!file.exists()) {  // 不存在返回 false
            return flag;
        } else {
            // 判断是否为文件
            if (file.isFile()) {  // 为文件时调用删除文件方法
                file.delete();
                flag = true;
            }
        }
        return flag;
    }

    /**
     * 往excel(xls)中写入(已存在的数据无法写入).
     * @param fileDir    文件路径
     * @param sheetName  表格索引
     * @param
     * @throws Exception
     */

    public static void writeToExcelXls(String fileDir, String sheetName, List<Map<String, Object>> mapList) throws Exception{
        System.out.println("=========="+fileDir);
        //创建workbook
        File file = new File(fileDir);

        try {
            xWorkbook = new XSSFWorkbook(new FileInputStream(file));

        }catch(FileNotFoundException e){
            e.printStackTrace();
        }catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        //文件流
        FileOutputStream fileOutputStream = null;
        XSSFSheet sheet = xWorkbook.getSheet(sheetName);
        // 获取表格的总行数
        // int rowCount = sheet.getLastRowNum() + 1; // 需要加一
        //获取表头的列数
        int columnCount = sheet.getRow(0).getLastCellNum();

        try {
            // 获得表头行对象
            XSSFRow titleRow = sheet.getRow(0);
            //创建单元格显示样式
            CellStyle cellStyle = xWorkbook.createCellStyle();
            cellStyle.setAlignment(HorizontalAlignment.LEFT);
            cellStyle.setVerticalAlignment(VerticalAlignment.BOTTOM);


            if(titleRow!=null){
                for(int rowId = 0; rowId < mapList.size(); rowId++){
                    Map<String, Object> map = mapList.get(rowId);
                    XSSFRow newRow=sheet.createRow(rowId+1);
                    newRow.setHeight((short)(20*20));//设置行高  基数为20

                    for (short columnIndex = 0; columnIndex < columnCount; columnIndex++) {  //遍历表头
                        //trim()的方法是删除字符串中首尾的空格
                        String mapKey = titleRow.getCell(columnIndex).toString().trim();
                        XSSFCell cell = newRow.createCell(columnIndex);
                        cell.setCellStyle(cellStyle);
                        cell.setCellValue(map.get(mapKey)==null ? null : map.get(mapKey).toString());
                    }
                }
            }

            fileOutputStream = new FileOutputStream(fileDir);
            xWorkbook.write(fileOutputStream);
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                if (fileOutputStream != null) {
                    fileOutputStream.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 获取用户信息的方法
     * @param cyPatients 数据库得到所有用户的数据
     * @return
     */
    public static void writeToExcel(List<CyPatient> cyPatients,String newPath) {
        //System.out.println(cyPatients);
        ////当前项目下路径
        //File file = new File("");
        //String filePath = null;
        //try {
        //    filePath = file.getAbsolutePath();
        //} catch (Exception e) {
        //    throw new RuntimeException(e);
        //}
        //
        String fileDir = newPath;
        //String fileDir = "工作簿1.xlsx";
        List<String> sheetName = new ArrayList<>();

        sheetName.add("用户表");

        System.out.println(sheetName);

        String[] title = {"id", "姓名", "注册时间","电话号码","性别","身份证号码","年龄","余额","身高","体重","密码"};
        createExcelXls(fileDir, sheetName, title);

        Map<String, List<Map<String, Object>>> users = getStringListMap(cyPatients);
        System.out.println(sheetName.size());


        createExcelXls(fileDir, sheetName, title);
        for (int j = 0; j < sheetName.size(); j++) {

            try {
                writeToExcelXls(fileDir, sheetName.get(j), users.get(sheetName.get(j)));
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }


        }
    }

    /**
     * 获取用户信息的方法
     * @param cyPatients 数据库得到所有用户的数据
     * @return
     */
    private static Map<String, List<Map<String, Object>>> getStringListMap(List<CyPatient> cyPatients) {
        List<Map<String, Object>> userList = new ArrayList<>();
        Map<String, List<Map<String, Object>>> users = new HashMap<>();
        //通过cyPatients来获取用户所有的东西
        //然后再一个个拿到cyPatient---》然后进行赋值
        for (int i = 0; i < cyPatients.size(); i++) {
            System.out.println(cyPatients.get(i));
            Map<String, Object> map = new HashMap<>();
            CyPatient cyPatient = cyPatients.get(i);//这样子就可以获取一个个用户对象
            map.put("id",cyPatient.getPatientId());
            map.put("姓名",cyPatient.getPatientName());
            map.put("注册时间",cyPatient.getPatientCreateTime());
            map.put("电话号码",cyPatient.getPatientPhone());
            map.put("性别",cyPatient.getPatientSex());
            map.put("身份证号码",cyPatient.getPatientIdCard());
            map.put("年龄",cyPatient.getPatientAge());
            map.put("余额",cyPatient.getPatientMoney());
            map.put("身高",cyPatient.getPatientHeight());
            map.put("体重",cyPatient.getPatientWeight());
            map.put("密码",cyPatient.getPatientPwd());
            userList.add(map);
            //将东西加入进去
            users.put("用户表", userList);

        }
        //最后返回
        return users;
    }
}
